{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQLite数据库\n",
    "\n",
    "SQLite是一种嵌入式数据库，它的数据库就是一个文件。由于SQLite本身是C写的，而且体积很小，所以，经常被集成到各种应用程序中，甚至在iOS和Android的App中都可以集成。\n",
    "\n",
    "windows 安装　[下载](https://www.sqlite.org/download.html)\n",
    "\n",
    "## 使用SQLite\n",
    "Python就内置了SQLite3，所以，在Python中使用SQLite，不需要安装任何东西，直接使用。\n",
    "\n",
    "在使用SQLite前，我们先要搞清楚几个概念：\n",
    "\n",
    "表是数据库中存放关系数据的集合，一个数据库里面通常都包含多个表，比如学生的表，班级的表，学校的表，等等。表和表之间通过外键关联。\n",
    "\n",
    "要操作关系数据库，首先需要连接到数据库，一个数据库连接称为Connection；\n",
    "\n",
    "连接到数据库后，需要打开游标，称之为Cursor，通过Cursor执行SQL语句，然后，获得执行结果。\n",
    "\n",
    "Python定义了一套操作数据库的API接口，任何数据库要连接到Python，只需要提供符合Python标准的数据库驱动即可。\n",
    "\n",
    "由于SQLite的驱动内置在Python标准库中，所以我们可以直接来操作SQLite数据库。\n",
    "\n",
    "我们在Python交互式命令行实践一下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入SQLite驱动:\n",
    "import sqlite3\n",
    "# 连接到SQLite数据库\n",
    "# 数据库文件是test.db\n",
    "# 如果文件不存在，会自动在当前目录创建:\n",
    "\n",
    "# 删掉已经存在的数据库\n",
    "db_file = 'test.db'\n",
    "if os.path.isfile(db_file):\n",
    "    os.remove(db_file)\n",
    "\n",
    "conn = sqlite3.connect('test.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建一个Cursor:\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 执行一条SQL语句，创建user表:\n",
    "cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 继续执行一条SQL语句，插入一条记录:\n",
    "cursor.execute(\"insert into user (id, name) values ('5', 'Mike')\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 通过rowcount获得插入的行数:\n",
    "print(\"行数\",cursor.rowcount)\n",
    "# 关闭Cursor:\n",
    "cursor.close()\n",
    "# 提交事务:\n",
    "conn.commit()\n",
    "# 关闭Connection:\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查询记录\n",
    "conn = sqlite3.connect('test.db')\n",
    "cursor = conn.cursor()\n",
    "# 执行查询语句:\n",
    "cursor.execute('select * from user' )\n",
    "# 获得查询结果集:\n",
    "print(cursor.fetchall())\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 练习\n",
    "import os, sqlite3\n",
    "\n",
    "db_file = 'exec.db'\n",
    "if os.path.isfile(db_file):\n",
    "    os.remove(db_file)\n",
    "\n",
    "# 创建表格\n",
    "conn = sqlite3.connect(db_file)\n",
    "cursor = conn.cursor()\n",
    "cursor.execute('create table student (id varchar(20) primary key, name varchar(20), score int)')\n",
    "\n",
    "# 插入数据\n",
    "cursor.execute(\"insert into student values ('A-001', 'Adam', 95)\")\n",
    "# 再插入两条数据\n",
    "cursor.execute(???)\n",
    "\n",
    "cursor.close()\n",
    "conn.commit()\n",
    "conn.close()\n",
    "\n",
    "\n",
    "def get_all():\n",
    "    # 连接到SQLite数据库\n",
    "    # 执行一条SQL语句\n",
    "    # 获得查询结果集\n",
    "    ???\n",
    "    print(all_value)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQLAlchemy\n",
    "\n",
    "SQLAlchemy是python的一个数据库ORM工具，提供了强大的对象模型间的转换，可以满足绝大多数数据库操作的需求，并且支持多种数据库引擎（sqlite，mysql，postgres, mongodb等)\n",
    "\n",
    "首先是连接到数据库，SQLALchemy支持多个数据库引擎，不同的数据库引擎连接字符串不一样，常用的有\n",
    "\n",
    "```\n",
    "数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名\n",
    "```\n",
    "\n",
    "```\n",
    "mssql+pymssql://username:password@hostname/database\n",
    "mssql://username:password@hostname/database\n",
    "postgresql://username:password@hostname/database\n",
    "sqlite:////absolute/path/to/database\n",
    "sqlite:///c:/absolute/path/to/database\n",
    "```\n",
    "更多连接字符串的介绍参见[https://docs.sqlalchemy.org/en/latest/core/engines.html?highlight=create_engine#database-urls]\n",
    "\n",
    "下面是连接和使用sqlite数据库的例子"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### connection\n",
    "使用传统的connection的方式连接和操作数据库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 数据库连接字符串\n",
    "DB_CONNECT_STRING = 'sqlite:///test.db'\n",
    "\n",
    "# 创建数据库引擎,echo为True,会打印所有的sql语句\n",
    "engine = create_engine(DB_CONNECT_STRING, echo=True)\n",
    "\n",
    "# 创建一个connection，这里的使用方式与python自带的sqlite的使用方式类似\n",
    "with engine.connect() as con:\n",
    "    # 执行sql语句，如果是增删改，则直接生效，不需要commit\n",
    "    rs = con.execute('select * from user')\n",
    "    data = rs.fetchone()\n",
    "    print(\"Data: %s\" % data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### connection事务\n",
    "使用事务可以进行批量提交和回滚"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DB_CONNECT_STRING = 'sqlite:///test.db'\n",
    "engine = create_engine(DB_CONNECT_STRING)\n",
    "\n",
    "with engine.connect() as connection:\n",
    "    trans = connection.begin()\n",
    "    try:\n",
    "        r0 = connection.execute(\"create table book (id varchar(20) primary key, name varchar(20), user_id varchar(20))\")\n",
    "        #r0 = connection.execute(\"create table user (id varchar(20) primary key, name varchar(20))\")\n",
    "    except:\n",
    "        print(\"已经有这个数据库，不用创建，继续...\")\n",
    "    try:\n",
    "        \n",
    "        r1 = connection.execute(\"insert into book (id,name, user_id) values ('3', 'Lucxx', '2')\")\n",
    "        r2 = connection.execute(\"select * from book\")\n",
    "        trans.commit()\n",
    "        print(r2.fetchall())\n",
    "    except:\n",
    "        trans.rollback()\n",
    "        raise"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## session\n",
    "connection是一般使用数据库的方式，sqlalchemy还提供了另一种操作数据库的方式，通过session对象，session可以记录和跟踪数据的改变，在适当的时候提交，并且支持强大的ORM的功能，下面是基本使用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.orm import sessionmaker"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 常用模式\n",
    "# 数据库连接字符串\n",
    "DB_CONNECT_STRING = 'sqlite:///test.db'\n",
    "\n",
    "# 创建数据库引擎,echo为True,会打印所有的sql语句\n",
    "engine = create_engine(DB_CONNECT_STRING, echo=True)\n",
    "\n",
    "# 创建会话类\n",
    "DB_Session = sessionmaker(bind=engine)\n",
    "\n",
    "# 创建会话对象\n",
    "session = DB_Session()\n",
    "\n",
    "# 在回话中处理数据库操作\n",
    "\"\"\"\n",
    "创建表\n",
    "获取数据\n",
    "插入数据\n",
    "修改数据\n",
    "\"\"\"\n",
    "# 如果再次运行，不要运行创建表\n",
    "#session.execute(\"create table member (id varchar(20) primary key, name varchar(20))\")\n",
    "\n",
    "session.execute(\"insert into member(id, name) values('3', '小样')\")\n",
    "session.commit() #来确认修改和增加的内容\n",
    "\n",
    "# 用完记得关闭，也可以用with\n",
    "session.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "上面创建了一个session对象，接下来可以操作数据库了，session也支持通过sql语句操作数据库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 练习　链接数据库，创建一个学生信息表(Student), 字段: id, name, age, 插入一条数据：　（1, Tom, 19）\n",
    "\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# 数据库链接设置\n",
    "DB_CONNECT_STRING = ???\n",
    "engine = create_engine(DB_CONNECT_STRING, echo=True)\n",
    "\n",
    "with engine.connect() as connection:\n",
    "    trans = ???\n",
    "    ???:\n",
    "        r0 = connection.???(\"create table Student (id varchar(20) primary key, name varchar(20), age int)\")\n",
    "    ???:\n",
    "        print(\"已经有这个数据库，不用创建，继续...\")\n",
    "    try:\n",
    "        \n",
    "        r1 = connection.???(\"insert into Student (id,name,age) values ('1', 'Tom', 19)\")\n",
    "        trans.???\n",
    "    except:\n",
    "        trans.rollback()\n",
    "        raise"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ORM\n",
    "\n",
    "Object-Relational Mapping，把关系数据库的表结构映射到对象上\n",
    "\n",
    "上面的member用class实例来表示"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Member(object):\n",
    "    def __init__(self, id, name):\n",
    "        self.id = id\n",
    "        self.name = name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 第一步，导入SQLAlchemy，并初始化DBSession："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入:\n",
    "from sqlalchemy import Column, String, create_engine\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "from sqlalchemy.ext.declarative import declarative_base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建对象的基类:\n",
    "Base = declarative_base()\n",
    "\n",
    "# 数据库连接字符串\n",
    "DB_CONNECT_STRING = 'sqlite:///db.sqlite'\n",
    "\n",
    "# 定义User对象:\n",
    "class User(Base):\n",
    "    # 表的名字:\n",
    "    __tablename__ = 'user'\n",
    "\n",
    "    # 表的结构:\n",
    "    id = Column(String(20), primary_key=True)\n",
    "    name = Column(String(20))\n",
    "\n",
    "# 初始化数据库连接:\n",
    "engine = create_engine(DB_CONNECT_STRING)\n",
    "# 创建DBSession类型:\n",
    "DBSession = sessionmaker(bind=engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "下面，我们看看如何向数据库表中添加一行记录。\n",
    "\n",
    "由于有了ORM，我们向数据库表中添加一行记录，可以视为添加一个User对象："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建session对象:\n",
    "session = DBSession()\n",
    "# 创建新User对象:\n",
    "new_user = User(id='2', name='Bob')\n",
    "# 添加到session:\n",
    "session.add(new_user)\n",
    "# 提交即保存到数据库:\n",
    "session.commit()\n",
    "# 关闭session:\n",
    "session.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 从数据库表中查询数据\n",
    "# 创建Session:\n",
    "session = DBSession()\n",
    "# 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:\n",
    "user = session.query(User).filter(User.id=='1').one()\n",
    "# 打印类型和对象的name属性:\n",
    "print('type:', type(user))\n",
    "print('name:', user.name)\n",
    "# 关闭Session:\n",
    "session.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可见，ORM就是把数据库表的行与相应的对象建立关联，互相转换。\n",
    "\n",
    "由于关系数据库的多个表还可以用外键实现一对多、多对多等关联，相应地，ORM框架也可以提供两个对象之间的一对多、多对多等功能。\n",
    "\n",
    "例如，如果一个User拥有多个Book，就可以定义一对多关系如下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy  import ForeignKey\n",
    "# 创建一个书的类\n",
    "class Book(Base):\n",
    "    __tablename__ = 'book'\n",
    "\n",
    "    id = Column(String(20), primary_key=True)\n",
    "    name = Column(String(20))\n",
    "    # “多”的一方的book表是通过外键关联到user表的:\n",
    "    user_id = Column(String(20), ForeignKey('user.id'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "当我们查询一个User对象时，该对象的books属性将返回一个包含若干个Book对象的list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建session对象:\n",
    "session = DBSession()\n",
    "# 创建新User对象:\n",
    "new_user = User(id='21', name='Kerry')\n",
    "# 添加到session:\n",
    "session.add(new_user)\n",
    "new_book = Book(id='10', name='Learn Python', user_id = new_user.id)\n",
    "print('书本名字：%s, 用户：%s' % (new_book.name, new_user.name))\n",
    "session.add(new_book)\n",
    "# 提交即保存到数据库:\n",
    "session.commit()\n",
    "# 关闭session:\n",
    "session.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 练习１　从数据库表中查询book数据\n",
    "# 创建Session:\n",
    "session = DBSession()\n",
    "# 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:\n",
    "book = session.query(???).filter(???.id=='3').one()\n",
    "# 打印结果\n",
    "print('book id', ???)\n",
    "print('book name:', ???)\n",
    "# 关闭Session:\n",
    "session.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 练习\n",
    "db_test.py\n",
    "\n",
    "### 总结\n",
    "db_example.py"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
